Phonepe_Project_final_VSL.ipynb
- Basic Python Script to Connect and Analyze
- Top 10 States by Total Transactions
- File
- Edit
- View
- Run
- Kernel
- Settings
- Help
Kernel status: Idle
[9]:
import pandas as pd
import mysql.connector
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib
Using matplotlib backend: module://matplotlib_inline.backend_inline
### Basic Python Script to Connect and Analyze
Basic Python Script to Connect and Analyze¶
[3]:
# ✅ MySQL connection
conn = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="phonepe"
)
# ✅ SQL Query
query = """
SELECT year, SUM(transaction_amount) AS total_amount
FROM aggregated_transaction
GROUP BY year
ORDER BY year;
"""
df = pd.read_sql(query, conn)
conn.close()
# ✅ Plot
plt.figure(figsize=(8,5))
plt.plot(df['year'], df['total_amount'], marker='o', color='purple')
plt.title("💰 Total Transactions Per Year")
plt.xlabel("Year")
plt.ylabel("Total Amount (₹)")
plt.grid(True)
plt.tight_layout()
plt.show()
C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\3934802578.py:17: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(query, conn) C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\3934802578.py:27: UserWarning: Glyph 128176 (\N{MONEY BAG}) missing from font(s) DejaVu Sans. plt.tight_layout() C:\Users\LENOVO\anaconda3\Lib\site-packages\IPython\core\pylabtools.py:170: UserWarning: Glyph 128176 (\N{MONEY BAG}) missing from font(s) DejaVu Sans. fig.canvas.print_figure(bytes_io, **kw)
### Top 10 States by Total Transactions
Top 10 States by Total Transactions¶
[11]:
query = """
SELECT state, SUM(transaction_amount) AS total_amount
FROM aggregated_transaction
GROUP BY state
ORDER BY total_amount DESC
LIMIT 10;
"""
df = pd.read_sql(query, conn)
# Plot
plt.figure(figsize=(10,6))
plt.barh(df['state'], df['total_amount'], color='skyblue')
plt.gca().invert_yaxis() # Highest on top
plt.title("🏆 Top 10 States by Total Transaction Amount")
plt.xlabel("Amount (₹)")
plt.tight_layout()
plt.show()
C:\Users\LENOVO\AppData\Local\Temp\ipykernel_4080\2086232993.py:9: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql(query, conn)
--------------------------------------------------------------------------- OperationalError Traceback (most recent call last) Cell In[11], line 9 1 query = """ 2 SELECT state, SUM(transaction_amount) AS total_amount 3 FROM aggregated_transaction (...) 6 LIMIT 10; 7 """ ----> 9 df = pd.read_sql(query, conn) 11 # Plot 12 plt.figure(figsize=(10,6)) File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:706, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype) 704 with pandasSQL_builder(con) as pandas_sql: 705 if isinstance(pandas_sql, SQLiteDatabase): --> 706 return pandas_sql.read_query( 707 sql, 708 index_col=index_col, 709 params=params, 710 coerce_float=coerce_float, 711 parse_dates=parse_dates, 712 chunksize=chunksize, 713 dtype_backend=dtype_backend, 714 dtype=dtype, 715 ) 717 try: 718 _is_table_name = pandas_sql.has_table(sql) File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:2738, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend) 2727 def read_query( 2728 self, 2729 sql, (...) 2736 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy", 2737 ) -> DataFrame | Iterator[DataFrame]: -> 2738 cursor = self.execute(sql, params) 2739 columns = [col_desc[0] for col_desc in cursor.description] 2741 if chunksize is not None: File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:2672, in SQLiteDatabase.execute(self, sql, params) 2670 raise TypeError("Query must be a string unless using sqlalchemy.") 2671 args = [] if params is None else [params] -> 2672 cur = self.con.cursor() 2673 try: 2674 cur.execute(sql, *args) File ~\anaconda3\Lib\site-packages\mysql\connector\connection_cext.py:841, in CMySQLConnection.cursor(self, buffered, raw, prepared, cursor_class, dictionary, read_timeout, write_timeout) 839 self.handle_unread_result(prepared) 840 if not self.is_connected(): --> 841 raise OperationalError("MySQL Connection not available.") 842 if read_timeout or write_timeout: 843 warnings.warn( 844 """The use of read_timeout after the connection has been established is unsupported 845 in the C-Extension""", 846 category=Warning, 847 ) OperationalError: MySQL Connection not available.
[ ]:
Notebook cell shifted up successfully
-
Variables
Callstack
Breakpoints
Source
9
1
Kernel Sources
Common Tools
No metadata.
Advanced Tools
No metadata.
Anaconda Assistant
4.1.0
What would you like to do?
The Anaconda Assistant is an AI-powered chat application designed to enhance the productivity of data scientists, developers, and researchers.
Get started
Working with dataframes
Anaconda Assistant
By debugging the active code cell, the error tied to the active code cell, and the
code of the active code cell will be sent to
OpenAI.
Start a new conversation
Chat
0%
full![Python [conda env:anaconda3] *](./Phonepe_Project_final_VSL_files/logo-64x64.png)